package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import bean.Data;
import util.DBUtil;

public class DataDao {
   DBUtil db=new DBUtil();
   Connection conn=db.open();
   PreparedStatement pstmt=null;
   ResultSet rs=null;
    
   
   //日报表查询
   public Map<String,Object> findConcetrition(String company_id,int outlet_id,String mondate){
	   Map<String,Object> map=new HashMap<String,Object>();
	   String sql="SELECT  DATE_FORMAT(mon_date,'%Y-%m-%d') days,cast(MAX(oil_con) as decimal(10,2))AS maxoil,cast(MIN(oil_con) as decimal(10,2)) AS minoil,cast(AVG(oil_con) as decimal(10,2)) AS avgoil,"
			   +"cast(MAX(unite) as decimal(10,2))AS maxunite,cast(MIN(unite) as decimal(10,2)) AS minunite,cast(AVG(unite) as decimal(10,2))AS avgunite,cast(MAX(temperature) as decimal(10,2)) AS maxtem,"
			   +"cast(MIN(temperature) as decimal(10,2)) AS mintem,cast(AVG(temperature) as decimal(10,2))AS avgtem,cast(MAX(humidity) as decimal(10,2))AS maxhum,cast(MIN(humidity) as decimal(10,2))AS minhum,"
			   +"cast(AVG(humidity) as decimal(10,2)) AS avghum,cast(MAX(pressure) as decimal(10,2)) AS maxpre,cast(MIN(pressure) as decimal(10,2))  AS minpre,cast(AVG(pressure) as decimal(10,2))  AS avgpre "
			   +"FROM monitorData,company,outlet  where monitorData.outlet_id=outlet.outlet_id and outlet.company_id=company.company_id and company.company_id='"+company_id+"' and outlet.outlet_id='"+outlet_id+"'"
			   		+ " and mon_date like '%"+mondate+"%' group by days";
	    try {

			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				
				map.put("days",rs.getDate(1));
				map.put("maxoil",rs.getFloat(2));
				map.put("minoil",rs.getFloat(3));
				map.put("avgoil",rs.getFloat(4));
				map.put("maxunite",rs.getFloat(5));
				map.put("minunite",rs.getFloat(6));
				map.put("avgunite",rs.getFloat(7));
				map.put("maxtem",rs.getFloat(8));
				map.put("mintem",rs.getFloat(9));
				map.put("avgtem",rs.getFloat(10));
				map.put("maxhum",rs.getFloat(11));
				map.put("minhum",rs.getFloat(12));
				map.put("avghum",rs.getFloat(13));			
				map.put("maxpre",rs.getFloat(14));
				map.put("minpre",rs.getFloat(15));
				map.put("avgpre",rs.getFloat(16));
						
			}			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.close();
		}	    
	   return map;
   }
   //周报表查询
   public List<Map> findWeekReport(String company_id,int outlet_id,String startdate,String enddate){
	   List<Map> list=new ArrayList<Map>();
	   String sql="SELECT  DATE_FORMAT(mon_date,'%Y-%m-%d') days,cast(MAX(oil_con) as decimal(10,2))AS maxoil,cast(MIN(oil_con) as decimal(10,2)) AS minoil,cast(AVG(oil_con) as decimal(10,2)) AS avgoil,"
			   +"cast(MAX(unite) as decimal(10,2))AS maxunite,cast(MIN(unite) as decimal(10,2)) AS minunite,cast(AVG(unite) as decimal(10,2))AS avgunite,cast(MAX(temperature) as decimal(10,2)) AS maxtem,"
			   +"cast(MIN(temperature) as decimal(10,2)) AS mintem,cast(AVG(temperature) as decimal(10,2))AS avgtem,cast(MAX(humidity) as decimal(10,2))AS maxhum,cast(MIN(humidity) as decimal(10,2))AS minhum,"
			   +"cast(AVG(humidity) as decimal(10,2)) AS avghum,cast(MAX(pressure) as decimal(10,2)) AS maxpre,cast(MIN(pressure) as decimal(10,2))  AS minpre,cast(AVG(pressure) as decimal(10,2))  AS avgpre "
			   +"FROM monitorData,company,outlet  where monitorData.outlet_id=outlet.outlet_id and outlet.company_id=company.company_id and company.company_id='"+company_id+"' and outlet.outlet_id='"+outlet_id+"'"
			   		+ " and mon_date between '"+startdate+"' and'"+enddate+"' group by days";
	    try {

			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				
				Map<String,Object> map=new HashMap<String, Object>();
				map.put("days",rs.getDate(1));
				map.put("maxoil",rs.getFloat(2));
				map.put("minoil",rs.getFloat(3));
				map.put("avgoil",rs.getFloat(4));
				map.put("maxunite",rs.getFloat(5));
				map.put("minunite",rs.getFloat(6));
				map.put("avgunite",rs.getFloat(7));
				map.put("maxtem",rs.getFloat(8));
				map.put("mintem",rs.getFloat(9));
				map.put("avgtem",rs.getFloat(10));
				map.put("maxhum",rs.getFloat(11));
				map.put("minhum",rs.getFloat(12));
				map.put("avghum",rs.getFloat(13));			
				map.put("maxpre",rs.getFloat(14));
				map.put("minpre",rs.getFloat(15));
				map.put("avgpre",rs.getFloat(16));
					
				list.add(map);
			}			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.close();
		}	    
	   return list;
   }
}
